﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using server.Model;

namespace server
{
    class VanetRIDal
    {
        private SQLiteDatabase sql_Excute = new SQLiteDatabase();

        /// <summary>
        /// 车辆注册
        /// </summary>
        /// <param name="vanet">车辆的车牌号加转Element后的字符串</param>
        /// <returns>是否注册成功</returns>
        public bool Create(VanetRID vanet)
        {
            try
            {
                var sql_query = "insert into VanetRID values(@RID,@RID_trans);";
                var cmdparams = new List<SQLiteParameter>()
                {
                    new SQLiteParameter("RID",vanet.RID),
                    new SQLiteParameter("RID_trans",vanet.RID_trans)
                };
                return sql_Excute.ExecuteNonQuery(sql_query, cmdparams);
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 查询数据库
        /// </summary>
        /// <param name="RID">车辆的车牌号</param>
        /// <returns>返回VanetRID</returns>
        public VanetRID GetbyRID(string RID)
        {
            try
            {
                var sql_query = "select * from VanetRID where RID=@RID;";
                var cmdparams = new List<SQLiteParameter>()
                {
                    new SQLiteParameter("RID",RID)
                };
                var dt = sql_Excute.GetDataTable(sql_query, cmdparams);
                if (dt.Rows.Count > 0)
                {
                    VanetRID vanet = new VanetRID();
                    vanet.RID = dt.Rows[0]["RID"].ToString();
                    vanet.RID_trans = dt.Rows[0]["RID_trans"].ToString();
                    return vanet;
                }
                else
                    return null;
            }
            catch (Exception)
            { return null; }
        }
        /// <summary>
        /// 查询数据库
        /// </summary>
        /// <param name="RID_trans">车辆的车牌号字符串</param>
        /// <returns>返回车牌号</returns>
        public string GetbyRID_trans(string RID_trans)
        {
            try
            {
                var sql_query_trace = "select * from VanetRID where RID_trans=@RID_trans;";
                var cmdparams_trace = new List<SQLiteParameter>()
                {
                    new SQLiteParameter("RID_trans",RID_trans)
                };
                var dt_trace = sql_Excute.GetDataTable(sql_query_trace, cmdparams_trace);
                if (dt_trace.Rows.Count > 0)
                {
                    //VanetRID vanet = new VanetRID();
                    string RID = dt_trace.Rows[0]["RID"].ToString();
                    //vanet.RID_trans = dt_trace.Rows[0]["RID_trans"].ToString();
                    return RID;
                }
                else
                    return null;
            }
            catch (Exception)
            { return null; }
        }

        /// <returns>返回所有车辆VanetRID列表</returns>
        public List<VanetRID> AllRID()
        {
            List<VanetRID> RID_all = new List<VanetRID>();
            try
            {
                var sql_query_all = "select * from VanetRID";
                var dt_all = sql_Excute.GetDataTable(sql_query_all);
                int i = 0;
                while (dt_all.Rows.Count > i)
                {
                    VanetRID vanet = new VanetRID();
                    vanet.RID = dt_all.Rows[i]["RID"].ToString();
                    vanet.RID_trans = dt_all.Rows[i]["RID_trans"].ToString();
                    RID_all.Add(vanet);
                    i++;
                }
                return RID_all;
            }
            catch (Exception)
            { return null; }
        }
        /// <summary>
        /// 查询数据库清除指定车辆
        /// </summary>
        /// <param name="RID">清除车辆的车牌号</param>
        /// <returns>返回是否成功</returns>
        public bool clearRID(string RID)
        {
            try
            {
                var sql_query_clear = "delete from VanetRID where RID=@RID;";
                var cmdparams_clear = new List<SQLiteParameter>()
                {
                    new SQLiteParameter("RID",RID)
                };
                var dt = sql_Excute.GetDataTable(sql_query_clear, cmdparams_clear);
                return true;
            }
            catch (Exception)
            { return false; }
        }
        /// <summary>
        /// 查询数据库清除所有车辆
        /// </summary>
        /// <returns>返回是否成功</returns>
        public bool clearAllRID()
        {
            try
            {
                var sql_query_clearall = "delete from VanetRID;";
                var dt = sql_Excute.GetDataTable(sql_query_clearall);
                return true;
            }
            catch (Exception)
            { return false; }
        }
    }
}
